declare namespace msds = "http://matsadaml.org/msds/v1.0"; 
declare variable $ProductGroup as xs:string external;

document
{
processing-instruction {"xml-stylesheet"} {"type='text/xsl' href='../../System/Transformations/index_products.xsl'"},
element index {
attribute productGroup {$ProductGroup},

let $companies := doc("../../ResourceFiles/CompanyDB.xml")
let $SubstanceDBs := doc("../../Substances/SubstanceDBs.xml")
let $SubstanceDB :=
    <DBs>
    {
    for $db in $SubstanceDBs/msds:substanceDBs/msds:db
    return
    	<DB key="{$db/@key}">
    		{doc(concat("../../Substances/", $db/msds:location))}
    	</DB>
    }
    </DBs>

for $product in collection(concat("../../Products/", $ProductGroup))/msds:product
    let $id := $product/@id
    let $name := $product/msds:name[1]/text()
    let $companyName := $companies//msds:company[@key = $product/msds:company/@key]//msds:companyName
    order by $companyName, $name
    return
        <product productId="{$id}" productName="{$name}" companyKey="{$product/msds:company/@key}" companyName="{$companyName}">
        {
        for $substance in  $product/msds:substances/msds:substance
        let $substanceName := $SubstanceDB//DB[@key = $substance/@db]//msds:substance-entry[@id = $substance/@id]//msds:name[1]
        order by $substance/@db
        return
            <substance db="{$substance/@db}" id="{$substance/@id}" name="{$substanceName}"/>
        }
        </product>
}
}
